﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DictManage.Code;
using System.IO;
using System.Data.OleDb;

namespace DictManage
{
    public partial class MainFrm : Form
    {
        public MainFrm()
        {
            InitializeComponent();
        }
        public void InitFrm()
        {

            DataTable table = OleDbAccess.GetDataSet("select * from TB_DictType").Tables[0];
            if (table != null && table.Rows.Count > 0)
            {
                this.treeView1.Nodes.Clear();
                AddTreeView("-1", this.treeView1.Nodes, table);
            }
        }
        public void AddTreeView(string fdm, TreeNodeCollection nodes, DataTable table)
        {
            DataRow[] drs = table.Select(" PID ='" + fdm + "' ");
            foreach (DataRow dr in drs)
            {
                string classid = dr["id"].ToString();
                string classname = dr["Name"].ToString();

                TreeNode main = new TreeNode();
                main.Text = classname;
                main.Tag = classid;
                nodes.Add(main);
                AddTreeView(classid, main.Nodes, table);
            }
        }
        private void MainFrm_Load(object sender, EventArgs e)
        {
            InitFrm();
        }
        string dicttypeName = "", dicttypeId = "";
        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            try
            {
                this.label1.Text = "您选择了【 " + this.treeView1.SelectedNode.Text + " 】大类";
                dicttypeName = this.treeView1.SelectedNode.Text;
                dicttypeId = this.treeView1.SelectedNode.Tag.ToString();
                DataTable table = OleDbAccess.GetDataSet("select  [id] as 编号, [name] as 项目名称,[value] as 项目值,[remark] as 备注,[seq] as 字典排序  from TB_DictData where DictType_ID='" + dicttypeId + "' order by lastupdated desc ").Tables[0];
                this.dataGridView1.DataSource = table;
                if (table == null || table.Rows.Count == 0)
                {
                    button2.Enabled = false;
                }
                else
                {
                    button2.Enabled = true;
                }
            }
            catch { }
        }
        private void treeView1_DoubleClick(object sender, EventArgs e) 
        {
            FrmTopLevel frm = new FrmTopLevel(dicttypeId, dicttypeName);
            frm.Tag = "编辑";
            frm.Text = "编辑 字典大类";
            frm.ShowDialog();
            InitFrm();
        }
        private void 添加字典大类ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FrmTopLevel frm = new FrmTopLevel(dicttypeId, dicttypeName);
            frm.Tag = "添加";
            frm.Text = "添加 字典大类";
            frm.ShowDialog();
            InitFrm();
        }
        private void 编辑字典大类ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FrmTopLevel frm = new FrmTopLevel(dicttypeId, dicttypeName);
            frm.Tag = "编辑";
            frm.Text = "编辑 字典大类";
            frm.ShowDialog();
            InitFrm();
        }
        private void 刷新列表ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            InitFrm();
        }
        private void 删除字典大类ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("删除大类，对应的小类也会删除！", "系统提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                string sql1 = " delete from TB_DictData where DictType_ID='" + dicttypeId + "' ";
                string sql2 = " delete from TB_DictType where ID='" + dicttypeId + "' ";
                OleDbAccess.ExecuteNonQury(sql1);
                OleDbAccess.ExecuteNonQury(sql2);
                InitFrm();
                MessageBox.Show("删除成功！", "系统提示");
            }
        }
        string TB_DictData_Id = null;
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            TB_DictData_Id = this.dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells[0].Value.ToString().Trim();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(dicttypeId) && !string.IsNullOrWhiteSpace(dicttypeName))
            {
                FrmManageDict frm = new FrmManageDict(dicttypeId, dicttypeName);
                frm.Tag = "添加";
                frm.Text = "添加 字典项目";
                frm.ShowDialog();
                treeView1_AfterSelect(null, null);
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(dicttypeId) && !string.IsNullOrWhiteSpace(dicttypeName) && !string.IsNullOrWhiteSpace(TB_DictData_Id))
            {
                FrmManageDict frm = new FrmManageDict(dicttypeId, dicttypeName, TB_DictData_Id);
                frm.Tag = "编辑";
                frm.Text = "编辑 字典项目";
                frm.ShowDialog();
                treeView1_AfterSelect(null, null);
            }
        }
        private void button3_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("是否删除！", "系统提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                for (int i = 0; i < this.dataGridView1.SelectedRows.Count; i++)
                {
                    string id = this.dataGridView1.SelectedRows[i].Cells[0].Value.ToString().Trim();
                    string sql1 = " delete from TB_DictData where ID='" + id + "' ";
                    OleDbAccess.ExecuteNonQury(sql1);
                }
                treeView1_AfterSelect(null, null);
                MessageBox.Show("删除成功！", "系统提示");
            }
        }
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            TB_DictData_Id = this.dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells[0].Value.ToString().Trim();
        }
        private void 入LCL库ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataTable table = OleDbAccess.GetDataSet("select * from TB_DictType where id='" + dicttypeId + "'").Tables[0];
            if (table != null && table.Rows.Count > 0)
            {
                AddDbDic("-1", table);
            }
            MessageBox.Show("当前选中的节点数据导入到LCL库已完成.");
        }
        #region 导入到LCL库中
        public void AddDbDic(string fdm, DataTable table)
        {
            DataRow[] drs = table.Select(" PID ='" + fdm + "' ");
            foreach (DataRow dr in drs)
            {
                string classid = dr["id"].ToString();
                string name = dr["Name"].ToString();
                string pid = dr["pid"].ToString();

                Guid parentId = pid == "-1" ? Guid.Empty : Guid.Parse(pid);

                CreateDbDicType(Guid.Parse(classid), name, name, false, 0, name, 0, parentId);

                AddDbDic(classid, table);
            }
        }
        public void CreateDbDicType(Guid id, string name, string code, bool isLast, int level, string nodePath, int orderby, Guid pid)
        {
            string sql = @"
INSERT INTO [DictType]([ID],[Name],[Code],[IsLast],[Level],[NodePath],[OrderBy],[ParentId],[IsDelete],[AddDate],[UpdateDate])
     VALUES('" + id + "','" + name + "','" + code + "',0," + level + ",'" + nodePath + "'," + orderby + ",'" + pid + "',0,getdate(),getdate())";

            DbHelperSQL.ExecuteSql(sql);

            DataTable table = OleDbAccess.GetDataSet(@"select  [id] as 编号, [name] as 项目名称,[value] as 项目值,[remark] as 备注,[seq] as 字典排序 
from TB_DictData where DictType_ID='" + id + "' order by lastupdated desc ").Tables[0];

            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow dr = table.Rows[i];
                    string 编号 = dr["编号"].ToString();
                    string 项目名称 = dr["项目名称"].ToString();
                    string 项目值 = dr["项目值"].ToString();
                    CreateDbDic(Guid.Parse(编号), 项目名称, 项目值, i, false, 1, name + "/" + 项目名称, i, Guid.Empty, id);
                }
                if (pid != Guid.Empty)
                {
                    level = 1;
                    orderby = 1;
                }
                DbHelperSQL.ExecuteSql("update DictType set IsLast=1,Level=" + level + " where ID='" + id + "'");
            }
        }
        public void CreateDbDic(Guid id, string name, string value, int order, bool isLast, int level, string nodePath, int orderby, Guid pid, Guid dtId)
        {
            string sql = @"INSERT INTO [Dictionary]
           ([ID],[Name],[Value],[Order],[IsLast],[Level],[NodePath],[OrderBy],[ParentId],[IsDelete],[AddDate],[UpdateDate],[DictType_ID])
     VALUES('" + id + "','" + name + "','" + value + "','" + order + "','0','0','" + nodePath + "'," + orderby + ",'" + pid + "','0',getdate(),getdate(),'" + dtId + "')";

            DbHelperSQL.ExecuteSql(sql);
        }
        #endregion
        private void 全部入LCL库ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataTable table = OleDbAccess.GetDataSet("select * from TB_DictType").Tables[0];
            if (table != null && table.Rows.Count > 0)
            {
                AddDbDic("-1", table);
            }
            MessageBox.Show("全部入LCL库完成.");
        }
        private void button4_Click(object sender, EventArgs e)
        {
            FrmManageDictList frm = new FrmManageDictList(dicttypeId, dicttypeName);
            frm.Tag = "批量添加";
            frm.Text = "批量添加 字典项目";
            frm.ShowDialog();
            treeView1_AfterSelect(null, null);
        }
        private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            TB_DictData_Id = this.dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells[0].Value.ToString().Trim();
            if (!string.IsNullOrWhiteSpace(dicttypeId) && !string.IsNullOrWhiteSpace(dicttypeName) && !string.IsNullOrWhiteSpace(TB_DictData_Id))
            {
                FrmManageDict frm = new FrmManageDict(dicttypeId, dicttypeName, TB_DictData_Id);
                frm.Tag = "编辑";
                frm.Text = "编辑 字典项目";
                frm.ShowDialog();
                treeView1_AfterSelect(null, null);
            }
        }
        #region treeView1节点移动
        private void treeView1_ItemDrag(object sender, ItemDragEventArgs e)
        {
            //设置拖放类型为移动
            DoDragDrop(e.Item, DragDropEffects.Move);
        }
        //保存前一个鼠标进入的节点
        private TreeNode MyOldNode;
        private void treeView1_DragEnter(object sender, DragEventArgs e)
        {
            //如果节点有数据，拖放目标允许移动
            object MyData = e.Data.GetData(typeof(TreeNode));
            //如果节点有数据，拖放目标允许移动
            if (MyData != null)
            {
                e.Effect = DragDropEffects.Move;
            }
            else
            {
                e.Effect = DragDropEffects.None;
            }
            TreeView MyTreeView = (TreeView)sender;
            TreeNode MyNode = MyTreeView.GetNodeAt(treeView1.PointToClient(new Point(e.X, e.Y)));
            if (MyNode != null)
            { //改变进入节点的背景色
                MyNode.BackColor = Color.Blue;
                //保存此节点，进入下一个时还原背景色
                MyOldNode = MyNode;
            }
        }
        private void treeView1_DragOver(object sender, DragEventArgs e)
        {
            //修改鼠标进入节点的背景色，还原上一个节点的背景色
            TreeView MyTreeView = (TreeView)sender;
            TreeNode MyNode = MyTreeView.GetNodeAt(treeView1.PointToClient(new Point(e.X, e.Y)));
            if ((MyNode != null) && (MyNode != MyOldNode))
            {
                MyOldNode.BackColor = Color.White;
                MyNode.BackColor = Color.Blue;
                MyOldNode = MyNode;
            }
        }
        private void treeView1_DragDrop(object sender, DragEventArgs e)
        {
            TreeNode MyNode = (TreeNode)e.Data.GetData(typeof(TreeNode));
            TreeView MyTreeView = (TreeView)sender;
            //得到当前鼠标进入的节点
            TreeNode MyTargetNode = MyTreeView.GetNodeAt(treeView1.PointToClient(new Point(e.X, e.Y)));
            if (MyTargetNode != null)
            {
                TreeNode MyTargetParent = MyTargetNode.Parent;
                //删除拖放的节点
                MyNode.Remove();
                //添加到目标节点
                MyTargetNode.Nodes.Add(MyNode);
                MyTargetNode.BackColor = Color.White;
                MyTreeView.SelectedNode = MyTargetNode;

                OleDbAccess.ExecuteNonQury("update TB_DictType set pid='" + MyTargetNode.Tag.ToString() + "' where ID='" + MyNode.Tag.ToString() + "'");
            }
        }
        #endregion
        private void xls导入ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dicttypeId.Length > 0)
            {
                #region MyRegion
                //导入指定文件夹下的xls文件数据到当前选中的字典中字典类型为文件名
                //xls数据结构必须包含 项目名称 项目值
                FolderBrowserDialog dialog = new FolderBrowserDialog();
                if (dialog.ShowDialog(this) == DialogResult.OK)
                {
                    string dir = dialog.SelectedPath;
                    DirectoryInfo Dir = new DirectoryInfo(dir);
                    foreach (FileInfo accounts in Dir.GetFiles("*.xls")) //查找文件  
                    {
                        DataSet dataSet = GetData(accounts.FullName);
                        DataTable table = dataSet.Tables[0];
                        if (table.Rows.Count > 0)
                        {
                            Guid dtId = Guid.NewGuid();
                            string sql = @"INSERT INTO [TB_DictType]([ID],[Name],[Remark],[Seq],[Editor],[LastUpdated],[PID])
                                            VALUES ('" + dtId + "','" + Path.GetFileNameWithoutExtension(accounts.FullName) + "','','','','" + DateTime.Now.ToString() + "','" + dicttypeId + "')";
                            OleDbAccess.ExecuteNonQury(sql);

                            for (int i = 0; i < table.Rows.Count; i++)
                            {
                                DataRow row = table.Rows[i];

                                string m_name = row["项目名称"].ToString();
                                string m_value = row["项目值"].ToString();

                                string m_DtId = dtId.ToString();
                                string m_Name = m_name;
                                string m_Value = m_value;
                                string m_Remark = "";
                                string m_Seq = i.ToString();
                                if (m_Name.Length > 1 && m_Value.Length > 1)
                                {
                                    sql = @"INSERT INTO [TB_DictData]([ID],[DictType_ID],[Name],[Value],[Remark],[Seq],[Editor],[LastUpdated])
                     VALUES ('" + Guid.NewGuid() + "','" + m_DtId + "','" + m_Name + "','" + m_Value + "','" + m_Remark + "','" + m_Seq + "','','" + DateTime.Now.ToString() + "')";
                                    OleDbAccess.ExecuteNonQury(sql);
                                }
                            }
                        }
                    }
                    InitFrm();
                    MessageBox.Show("导入完成.");
                }
                #endregion
            }
            else
            {
                MessageBox.Show("请选择要导入的根节点.");
            }
        }
        #region MyRegion
        private DataSet GetData(string filename)
        {
            try
            {
                DataSet ds;
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                "Extended Properties=Excel 8.0;" +
                                "data source=" + filename;
                OleDbConnection myConn = new OleDbConnection(strCon);
                string strCom = " SELECT * FROM [Sheet1$]";
                myConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
                ds = new DataSet();
                myCommand.Fill(ds);
                myConn.Close();
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("该Excel文件的工作表的名字不正确," + ex.Message);
                return null;
            }
        }
        #endregion
    }
}
